insert overwrite table jms_dm.dm_dev_center_operate_summary_dt partition (dt)
select network_code                                --到件站点code
     , network_name                                --到件站点name
     , out_way_num                                 --出港量
     , out_pack_way_num                            --出港卸车包内票量
     , out_zddev_way_num                           --出港自动建包量
     , out_rgdev_way_num                           --出港人工建包量
     , same_way_num                                --同城量
     , same_pack_way_num                           --同城卸车包内票量
     , same_zddev_way_num                          --同城自动建包量
     , same_rgdev_way_num                          --同城人工建包量
     , transfer_way_num                            --中转量
     , transfer_pack_way_num                       --中转卸车包内票量
     , in_way_num                                  --进港量
     , in_pack_way_num                             --进港卸车包内票量
     , in_tear_pack_num                            --进港拆包量
     , in_tear_way_num                             --进港拆包包内票量
     , in_build_pack_num                           --进港建包量
     , null                       as in_way_total  --归属出港量
     , null                       as out_way_total --归属出港量
     , date_add('{{ execution_date | cst_ds }}', -1) as date_time     --日期
     , t4.agent_code
     , t4.agent_name
     , same_city_port_type
     , transfer_port_type
     , date_add('{{ execution_date | cst_ds }}', -1) as dt
from (
         select network_code                                        --到件站点code
              , network_name                                        --到件站点name
              , same_city_port_type                                 --同城归属 1：进港 2：出港
              , transfer_port_type                                  --中转归属 1：进港 2：出港
              , sum(out_way_num)           as out_way_num           --出港量
              , sum(out_pack_way_num)      as out_pack_way_num      --出港卸车包内票量
              , sum(out_zddev_way_num)     as out_zddev_way_num     --出港自动建包量
              , sum(out_rgdev_way_num)     as out_rgdev_way_num     --出港人工建包量
              , sum(same_way_num)          as same_way_num          --同城量
              , sum(same_pack_way_num)     as same_pack_way_num     --同城卸车包内票量
              , sum(same_zddev_way_num)    as same_zddev_way_num    --同城自动建包量
              , sum(same_rgdev_way_num)    as same_rgdev_way_num    --同城人工建包量
              , sum(transfer_way_num)      as transfer_way_num      --中转量
              , sum(transfer_pack_way_num) as transfer_pack_way_num --中转卸车包内票量
              , sum(in_way_num)            as in_way_num            --进港量
              , sum(in_pack_way_num)       as in_pack_way_num       --进港卸车包内票量
              , sum(in_tear_pack_num)      as in_tear_pack_num      --进港拆包量
              , sum(in_tear_way_num)       as in_tear_way_num       --进港拆包包内票量
              , sum(in_build_pack_num)     as in_build_pack_num     --进港建包量
         from (
                  select network_code                                                                         --到件站点code
                       , network_name                                                                         --到件站点name
                       , same_city_port_type                                                                  --同城归属 1：进港 2：出港
                       , transfer_port_type                                                                   --中转归属 1：进港 2：出港
                       , sum(if(type = 1, 1, 0))                                     as out_way_num           --出港票量
                       , sum(if(type = 1 and (arrival_packagecode is not null and arrival_packagecode <> ''), 1,
                                0))                                                  as out_pack_way_num      --出港卸车包内票量
                       , sum(if(type = 1 and send_build_bag_time is not null and source_arrival <> '移动端', 1,
                                0))                                                  as out_zddev_way_num     --出港自动建包票量
                       , sum(if(type = 1 and send_build_bag_time is not null and source_arrival = '移动端', 1,
                                0))                                                  as out_rgdev_way_num     --出港人工建包票量
                       , sum(if(type = 2, 1, 0))                                     as same_way_num          --同城票量
                       , sum(if(type = 2 and (arrival_packagecode is not null and arrival_packagecode <> ''), 1,
                                0))                                                  as same_pack_way_num     --同城卸车包内票量
                       , sum(if(type = 2 and send_build_bag_time is not null and source_arrival <> '移动端', 1,
                                0))                                                  as same_zddev_way_num    --同城自动建包票量
                       , sum(if(type = 2 and send_build_bag_time is not null and source_arrival = '移动端', 1,
                                0))                                                  as same_rgdev_way_num    --同城人工建包票量
                       , sum(if(type = 3, 1, 0))                                     as transfer_way_num      --中转票量
                       , sum(if(type = 3 and (arrival_packagecode is not null and arrival_packagecode <> ''), 1,
                                0))                                                  as transfer_pack_way_num --中转卸车包内票量
                       , sum(if(type = 4, 1, 0))                                     as in_way_num            --进港票量
                       , sum(if(type = 4 and (arrival_packagecode is not null and arrival_packagecode <> ''), 1,
                                0))                                                  as in_pack_way_num       --进港卸车包内票量
                       , 0                                                           as in_tear_pack_num      --进港拆包票量
                       , sum(if(type = 4 and send_pack_falg = 1, 1, 0))              as in_tear_way_num       --进港拆包包内票量
                       , sum(if(type = 4 and send_build_bag_time is not null, 1, 0)) as in_build_pack_num     --进港建包票量 
                  from jms_dm.dm_dev_center_operate_waybill_detail_dt
                  where dt = date_add('{{ execution_date | cst_ds }}', -1)
                  group by network_code        --到件站点code
                         , network_name        --到件站点name
                         , same_city_port_type --同城归属 1：进港 2：出港
                         , transfer_port_type  --中转归属 1：进港 2：出港
                  union all
                  select network_code                      --到件站点code
                       , network_name                      --到件站点name
                       , same_city_port_type               --同城归属 1：进港 2：出港
                       , transfer_port_type                --中转归属 1：进港 2：出港
                       , 0        as out_way_num           --出港量
                       , 0        as out_pack_way_num      --出港卸车包内票量
                       , 0        as out_zddev_way_num     --出港自动建包量
                       , 0        as out_rgdev_way_num     --出港人工建包量
                       , 0        as same_way_num          --同城量
                       , 0        as same_pack_way_num     --同城卸车包内票量
                       , 0        as same_zddev_way_num    --同城自动建包量
                       , 0        as same_rgdev_way_num    --同城人工建包量
                       , 0        as transfer_way_num      --中转量
                       , 0        as transfer_pack_way_num --中转卸车包内票量
                       , 0        as in_way_num            --进港量
                       , 0        as in_pack_way_num       --进港卸车包内票量
                       , count(1) as in_tear_pack_num      --进港拆包量
                       , 0        as in_tear_way_num       --进港拆包包内票量
                       , 0        as in_build_pack_num     --进港建包量
                  from (
                           select network_code        --到件站点code
                                , network_name        --到件站点name
                                , same_city_port_type --同城归属 1：进港 2：出港
                                , transfer_port_type  --中转归属 1：进港 2：出港
                                , arrival_packagecode
                           from jms_dm.dm_dev_center_operate_waybill_detail_dt
                           where dt = date_add('{{ execution_date | cst_ds }}', -1)
                             and type = 4
                             and send_pack_falg = 1
                           group by network_code        --到件站点code
                                  , network_name        --到件站点name
                                  , arrival_packagecode
                                  , same_city_port_type --同城归属 1：进港 2：出港
                                  , transfer_port_type --中转归属 1：进港 2：出港
                       ) a
                  group by network_code        --到件站点code
                         , network_name        --到件站点name
                         , same_city_port_type --同城归属 1：进港 2：出港
                         , transfer_port_type  --中转归属 1：进港 2：出港 
              ) a
         group by network_code        --到件站点code
                , network_name        --到件站点name
                , same_city_port_type --同城归属 1：进港 2：出港
                , transfer_port_type --中转归属 1：进港 2：出港
     ) a
         left join jms_dim.dim_network_whole_massage t4
                   on a.network_code = t4.code
    distribute by dt
;
